package com.deng.common.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.dao.IEmployee;
import com.deng.common.vo.DispenseOrganizationVo;
import com.deng.common.vo.EmployeeVo;
import com.deng.common.vo.OrganizationVo;
import com.deng.util.DateTools;

public class EmployeeImpl extends SQLHelper implements IEmployee {

	public List<EmployeeVo> addAndGetEmployee(EmployeeVo vo) {
		try{
//			Connection con = getConnect();
//			ResultSet rs = queryData(con,"select case when max(employee_id)+1 is null then 1 else max(employee_id)+1 end as employee_id" +
//					" from t_sys_stat_employee");	
//			int maxID = 0;
//			if(rs.next()){
//				maxID = rs.getInt("employee_id");
//			}
//			
//			StringBuffer sb = new StringBuffer("insert into t_sys_stat_employee" +
//					" (employee_id,name,work_id,his_id,in_use,birthday,in_hospital_date) " +
//					" values("+maxID+",'"+vo.name+"',");
//			if(vo.work_id != null && !vo.work_id.trim().equals("")){
//				sb.append("'"+vo.work_id+"','"+vo.work_id+"'");
//			}else{
//				sb.append("null,null,");
//			}
//			vo.id = maxID;
//			
//			sb.append("1,'"+vo.birthday_str+"','"+vo.in_hospital_date_str+"')");
//			
//			updateData(con,sb.toString());
//			
//			
//			firstResOnMonthTable(vo,con);
//			
//			String sql = "select * from t_sys_stat_employee where in_use = 1 order by name";
//			rs = queryData(con,sql);
//			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
//			while(rs.next()){
//				vo = new EmployeeVo();
//				vo.id = rs.getInt("employee_id");
//				vo.name = rs.getString("name");
//				vo.birthday_str = DateTools.dtsJustDate(rs.getDate("birthday"));
//				vo.work_id = rs.getString("work_id");
//				vo.in_hospital_date_str = DateTools.dtsJustDate(rs.getDate("in_hospital_date"));
//				
//				l.add(vo);
//			}
//			
//			freeConnection(con);
//			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

//	private String getOrganizationName(List<OrganizationVo> orgList,Integer id){
//		if(id == null){
//			return null;
//		}
//		for(int i =0;i<orgList.size();i++){
//			OrganizationVo vo = orgList.get(i);
//			if(vo.id == id){
//				return vo.full_name;
//			}
//		}
//		return null;
//	}
//	private String getDispenseName(List<DispenseOrganizationVo> orgList,Integer id){
//		if(id == null){
//			return null;
//		}
//		for(int i =0;i<orgList.size();i++){
//			DispenseOrganizationVo vo = orgList.get(i);
//			if(vo.id == id){
//				return vo.name;
//			}
//		}
//		return null;
//	}
	public List<EmployeeVo> getAllEmployeeInfo() {
		try{
//			List<OrganizationVo> orgList = new OrganizationImpl().getAllOrganizationByFullName(1);
			String sql = " select a.employee_id,a.name,a.sfz,a.xztm,a.his_id,a.work_id,a.daoshixingming,"  +
					" a.xingbie,a.in_use,a.renyuanxingzhi,a.shifoufanpin,"  +
					" a.shenfen,DICT_SHENFEN.name as shenfen_str,"  +
					" a.yonggongzhidu,DICT_YONGGONGZHIDU.name as yonggongzhidu_str,"  +
					" a.xingzhengkeshi,t_sys_stat_admin_department.name as xingzhengkeshi_str,"  +
					" a.keshixingzhi,DICT_KESHIXINGZHI.name as keshixingzhi_str,"  +
					" a.gongzuojixiaohesuandanyuan," +
					"	t_sys_stat_organization_vs_dispense_organization.name as gongzuojixiaohesuandanyuan_str,"  +
					" a.jingyingjixiaohesuandanyuan," +
					"	t_sys_stat_operate_department.name as jingyingjixiaohesuandanyuan_str,"  +
					" a.zhiweibiaozhunfen,a.fengxianxishu,"  +
					" a.xingzhengzhiwu1,b.name as xingzhengzhiwu1_str,"  +
					" a.xingzhengzhiwu2,c.name as xingzhengzhiwu2_str,"  +
					" a.xingzhengjibie,DICT_XINGZHENGJIBIE.name as xingzhengjibie_str,"  +
					" a.xingzhengshizhi,DICT_XINGZHENGSHIZHI.name as xingzhengshizhi_str,"  +
					" a.chushengriqi,a.ruzhishijian,a.ruyuanshijian,"  +
					" a.gangweimingcheng,DICT_GANGWEIMINGCHENG.name as gangweimingcheng_str,"  +
					" a.gangweijibie,a.linchuangjishuma,a.teshubiaozhi,"  +
					" a.gangweileibie,DICT_GANGWEILEIBIE.name as gangweileibie_str,"  +
					" a.jishuzhicheng,DICT_JISHUZHICHENG.name as jishuzhicheng_str,"  +
					" a.xianpinzhicheng,d.name as xianpinzhicheng_str,"  +
					" a.zhichengjibie,e.name as zhichengjibie_str,"  +
					" a.zhiyeleibie,DICT_DUTY_TYPE.name as zhiyeleibie_str,"  +
					" a.zhuguankeshi,a.fenguankeshi from t_sys_stat_employee as a"  +
					" left join DICT_SHENFEN"  +
					" on a.shenfen=DICT_SHENFEN.id"  +
					" left join DICT_YONGGONGZHIDU"  +
					" on a.yonggongzhidu = DICT_YONGGONGZHIDU.id"  +
					" left join t_sys_stat_admin_department"  +
					" on a.xingzhengkeshi = t_sys_stat_admin_department.id"  +
					" left join DICT_KESHIXINGZHI"  +
					" on a.keshixingzhi = DICT_KESHIXINGZHI.id"  +
					" left join t_sys_stat_organization_vs_dispense_organization"  +
					" on a.gongzuojixiaohesuandanyuan = t_sys_stat_organization_vs_dispense_organization.id"  +
					" left join t_sys_stat_operate_department"  +
					" on a.jingyingjixiaohesuandanyuan = t_sys_stat_operate_department.id"  +
					" left join DICT_XINGZHENGZHIWU as b"  +
					" on a.xingzhengzhiwu1 = b.id"  +
					" left join DICT_XINGZHENGZHIWU as c"  +
					" on a.xingzhengzhiwu2 = c.id"  +
					" left join DICT_XINGZHENGJIBIE"  +
					" on a.xingzhengjibie = DICT_XINGZHENGJIBIE.id"  +
					" left join DICT_XINGZHENGSHIZHI"  +
					" on a.xingzhengshizhi = DICT_XINGZHENGSHIZHI.id"  +
					" left join DICT_GANGWEIMINGCHENG"  +
					" on a.gangweimingcheng = DICT_GANGWEIMINGCHENG.id"  +
					" left join DICT_GANGWEILEIBIE"  +
					" on a.gangweileibie = DICT_GANGWEILEIBIE.id"  +
					" left join DICT_JISHUZHICHENG"  +
					" on a.jishuzhicheng = DICT_JISHUZHICHENG.id"  +
					" left join DICT_ZHICHENGJIBIE as d"  +
					" on a.xianpinzhicheng = d.id"  +
					" left join DICT_ZHICHENGJIBIE as e"  +
					" on a.zhichengjibie = e.id"  +
					" left join DICT_DUTY_TYPE"  +
					" on a.zhiyeleibie = DICT_DUTY_TYPE.id";
			
			Connection conn = getConnect();
			
			ResultSet rs = queryData(conn,sql);
			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
			while(rs.next()){
				EmployeeVo vo = new EmployeeVo();
				vo.id = rs.getInt("employee_id");
				vo.name = rs.getString("name");
				vo.xingbie = rs.getInt("xingbie");
				vo.sfz = rs.getString("sfz");
				vo.xztm = rs.getString("xztm");
				vo.his_id = rs.getString("his_id");
				vo.work_id = rs.getString("work_id");
				vo.in_use = rs.getInt("in_use");
				vo.shenfen = rs.getInt("shenfen");
				vo.shenfen_str = rs.getString("shenfen_str");
				vo.renyuanxingzhi = rs.getInt("renyuanxingzhi");
				vo.shifoufanpin = rs.getInt("shifoufanpin");
				vo.yonggongzhidu = rs.getInt("yonggongzhidu");
				vo.yonggongzhidu_str = rs.getString("yonggongzhidu_str");
				vo.daoshixingming = rs.getString("daoshixingming");
				vo.xingzhengkeshi = rs.getInt("xingzhengkeshi");
				vo.xingzhengkeshi_str = rs.getString("xingzhengkeshi_str");
				vo.keshixingzhi = rs.getInt("keshixingzhi");
				vo.keshixingzhi_str = rs.getString("keshixingzhi_str");
				vo.gongzuojixiaohesuandanyuan = rs.getInt("gongzuojixiaohesuandanyuan");
				vo.gongzuojixiaohesuandanyuan_str = rs.getString("gongzuojixiaohesuandanyuan_str");//getOrganizationName(orgList,rs.getInt("gongzuojixiaohesuandanyuan"));
				vo.jingyingjixiaohesuandanyuan = rs.getInt("jingyingjixiaohesuandanyuan");
				vo.jingyingjixiaohesuandanyuan_str = rs.getString("jingyingjixiaohesuandanyuan_str");
				if(rs.getString("zhiweibiaozhunfen") != null){
					vo.zhiweibiaozhunfen_str = rs.getBigDecimal("zhiweibiaozhunfen").toString();
				}
				if(rs.getString("fengxianxishu") != null){
					vo.fengxianxishu_str = rs.getBigDecimal("fengxianxishu").toString();
				}
				
				vo.xingzhengzhiwu1 = rs.getInt("xingzhengzhiwu1");
				vo.xingzhengzhiwu1_str = rs.getString("xingzhengzhiwu1_str");
				vo.xingzhengzhiwu2 = rs.getInt("xingzhengzhiwu2");
				vo.xingzhengzhiwu2_str = rs.getString("xingzhengzhiwu2_str");
				vo.xingzhengjibie = rs.getInt("xingzhengjibie");
				vo.xingzhengjibie_str = rs.getString("xingzhengjibie_str");
				vo.xingzhengshizhi = rs.getInt("xingzhengshizhi");
				vo.xingzhengshizhi_str = rs.getString("xingzhengshizhi_str");
				vo.chushengriqi_str = DateTools.dtsJustDate(rs.getDate("chushengriqi"));
				vo.ruzhishijian_str = DateTools.dtsJustDate(rs.getDate("ruzhishijian"));
				vo.ruyuanshijian_str = DateTools.dtsJustDate(rs.getDate("ruyuanshijian"));
				vo.gangweimingcheng = rs.getInt("gangweimingcheng");
				vo.gangweimingcheng_str = rs.getString("gangweimingcheng_str");
				vo.gangweijibie = rs.getInt("gangweijibie");
				vo.gangweileibie = rs.getInt("gangweileibie");
				vo.gangweileibie_str = rs.getString("gangweileibie_str");
				vo.linchuangjishuma = rs.getString("linchuangjishuma");
				vo.jishuzhicheng = rs.getInt("jishuzhicheng");
				vo.jishuzhicheng_str = rs.getString("jishuzhicheng_str");
				vo.xianpinzhicheng = rs.getInt("xianpinzhicheng");
				vo.xianpinzhicheng_str = rs.getString("xianpinzhicheng_str");
				vo.zhichengjibie = rs.getInt("zhichengjibie");
				vo.zhichengjibie_str = rs.getString("zhichengjibie_str");
				vo.zhiyeleibie = rs.getInt("zhiyeleibie");
				vo.zhiyeleibie_str = rs.getString("zhiyeleibie_str");
				vo.teshubiaozhi = rs.getInt("teshubiaozhi");
				vo.zhuguankeshi = rs.getString("zhuguankeshi");
				vo.fenguankeshi = rs.getString("fenguankeshi");
				
							
				l.add(vo);
			}
			
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<EmployeeVo> getEmployeeByDispenseDate(
			int dispenseId, String date) {
		try{
//			String sql = "select * from t_stat_employee_vs_dispense_organization_month," +
//					"  t_stat_employee_vs_post_month where" +
//					"  t_stat_employee_vs_dispense_organization_month.employee_id = t_stat_employee_vs_post_month.employee_id" +
//					"  and t_stat_employee_vs_dispense_organization_month.dispense_organization_id = t_stat_employee_vs_post_month.dispense_organization_id" +
//					"  and t_stat_employee_vs_dispense_organization_month.stat_time = t_stat_employee_vs_post_month.stat_time" +
//					"  and t_stat_employee_vs_dispense_organization_month.dispense_organization_id = "+dispenseId +
//					"  and t_stat_employee_vs_dispense_organization_month.stat_time = '"+date+"'"+
//					"  order by t_stat_employee_vs_dispense_organization_month.employee_name";
//			
//			Connection conn = getConnect();
//			
//			ResultSet rs = queryData(conn,sql);
//			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
//			while(rs.next()){
//				EmployeeVo vo = new EmployeeVo();
//				vo.id = rs.getInt("employee_id");
//				vo.name = rs.getString("employee_name");
//				vo.dispense_id = rs.getInt("dispense_organization_id");
//				vo.dispense_name = rs.getString("dispense_organization_name");
//				vo.stat_time_str = DateTools.dtsJustDate(rs.getDate("stat_time"));
//				vo.formula = rs.getString("formula");
//				vo.post_id = rs.getInt("post_id");
//				vo.post_point = rs.getString("point");
//				vo.post_name = rs.getString("post_name");
//				
//				
//				l.add(vo);
//			}
//			
//			freeConnection(conn);
//			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<EmployeeVo> delAndGetEmployee(EmployeeVo vo) {
		try{
//			Connection con = getConnect();
//			String sql = "update t_sys_stat_employee set in_use = 0 where employee_id ="+vo.id;;
//			
//			updateData(con,sql);
//			sql = "select * from t_sys_stat_employee where in_use = 1 order by name";
//			ResultSet rs = queryData(con,sql);
//			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
//			while(rs.next()){
//				vo = new EmployeeVo();
//				vo.id = rs.getInt("id");
//				vo.name = rs.getString("name");
//				vo.birthday_str = DateTools.dtsJustDate(rs.getDate("birthday"));
//				vo.work_id = rs.getString("work_id");
//				vo.in_hospital_date_str = DateTools.dtsJustDate(rs.getDate("in_hospital_date"));
//				
//				l.add(vo);
//			}
//			
//			freeConnection(con);
//			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	
	public static void main(String s[]){
		Calendar caNow = Calendar.getInstance();
		caNow.clear();
		caNow.set(Calendar.getInstance().getTime().getYear()+1900, Calendar.getInstance().getTime().getMonth(), 1);
		
		System.out.println(caNow.getTime());
	}
	private void firstResOnMonthTable(EmployeeVo vo,Connection con) {
		try{
//			Calendar caNow = Calendar.getInstance();
//			caNow.clear();
//			caNow.set(Calendar.getInstance().getTime().getYear()+1900, Calendar.getInstance().getTime().getMonth()+1, 1);
//			
//			Calendar caIn = Calendar.getInstance();
//			caIn.clear();
//			caIn.set(Integer.valueOf(vo.in_hospital_date_str.split("-")[0]),Integer.valueOf(vo.in_hospital_date_str.split("-")[1]), 1);
//			
//			
////			Connection con = getConnect();
//			while(caIn.before(caNow)){
//				String sql1 = "insert into t_stat_employee_vs_post_month " +
//						"  (employee_id,post_id,post_name,stat_time,dispense_organization_id,point)" +
//						"  values("+vo.id+","+vo.post_id+",'"+vo.post_name+"','"+DateTools.dtsWithTimeT0(caIn.getTime())+"'," +
//						"	"+vo.dispense_id+","+vo.post_point+")";
//				updateData(con,sql1);
//				
//				String sql2 = "insert into t_stat_employee_vs_dispense_organization_month " +
//						"  (employee_id,employee_name,dispense_organization_id,dispense_organization_name,stat_time)" +
//						"  values("+vo.id+",'"+vo.name+"',"+vo.dispense_id+",'"+vo.dispense_name+"','"+DateTools.dtsWithTimeT0(caIn.getTime())+"')";
//				updateData(con,sql2);
//				
//				caIn.set(caIn.getTime().getYear()+1900, caIn.getTime().getMonth()+1, 1);
//			}
////			freeConnection(con);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public List<EmployeeVo> getEmployeeBonusByDispenseDate(int dispenseId,
			String date) {
		try{
//			Connection conn = getConnect();
//			String formulaSQL = "select dbo.FUN_DispenseBonus("+dispenseId+",'"+date+"')";
//			ResultSet rs = queryData(conn,formulaSQL);
//			if(rs.next()){
//				String formula = rs.getString(1);
//				System.out.println(formula);
//				String dispenseBonusSQL = "select "+formula;
//				rs = queryData(conn,dispenseBonusSQL);
//				if(rs.next()){
//					BigDecimal dispenseBonus = rs.getBigDecimal(1);
//				}
//				
//				
//				
//			}
//			
//			String sql = "select * from t_stat_employee_vs_dispense_organization_month," +
//					"  t_stat_employee_vs_post_month where" +
//					"  t_stat_employee_vs_dispense_organization_month.employee_id = t_stat_employee_vs_post_month.employee_id" +
//					"  and t_stat_employee_vs_dispense_organization_month.dispense_organization_id = t_stat_employee_vs_post_month.dispense_organization_id" +
//					"  and t_stat_employee_vs_dispense_organization_month.stat_time = t_stat_employee_vs_post_month.stat_time" +
//					"  and t_stat_employee_vs_dispense_organization_month.dispense_organization_id = "+dispenseId +
//					"  and t_stat_employee_vs_dispense_organization_month.stat_time = '"+date+"'"+
//					"  order by t_stat_employee_vs_dispense_organization_month.employee_name";
//			
//			
//			
//			
//			while(rs.next()){
//				
//				
//				
//			}
//			
//			
//			
//			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
//			while(rs.next()){
//				EmployeeVo vo = new EmployeeVo();
//				vo.id = rs.getInt("employee_id");
//				vo.name = rs.getString("employee_name");
//				vo.dispense_id = rs.getInt("dispense_organization_id");
//				vo.dispense_name = rs.getString("dispense_organization_name");
//				vo.stat_time_str = DateTools.dtsJustDate(rs.getDate("stat_time"));
//				vo.formula = rs.getString("formula");
//				vo.post_id = rs.getInt("post_id");
//				vo.post_point = rs.getString("point");
//				vo.post_name = rs.getString("post_name");
//				
//				l.add(vo);
//			}
//			
//			freeConnection(conn);
//			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public BigDecimal getEmployeeBonus(String formulaStr,int dispenseId,int employeeId,String date) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<EmployeeVo> getEmployeeByDispenseYM(int dispenseId, int y, int m) {

		try{
			String sql = "select * from t_stat_employee_month " +
					"	where gongzuojixiaohesuandanyuan = "+dispenseId+
						" 	and year(stat_time) = "+y+
						"	and month(stat_time) = "+m+
						"	order by name";
			
			Connection conn = getConnect();
			
			ResultSet rs = queryData(conn,sql);
			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
			while(rs.next()){
				EmployeeVo vo = new EmployeeVo();
				vo.id = rs.getInt("employee_id");
				vo.name = rs.getString("name");
				vo.gongzuojixiaohesuandanyuan= dispenseId;
				vo.work_id = rs.getString("work_id");
				vo.stat_time = DateTools.dtsJustDate(rs.getDate("stat_time"));
				vo.cal_gongzuojixiao = rs.getBigDecimal("cal_gongzuojixiao")!=null?rs.getBigDecimal("cal_gongzuojixiao").toString():"0";
				vo.pay_gongzuojixiao = rs.getBigDecimal("pay_gongzuojixiao")!=null?rs.getBigDecimal("pay_gongzuojixiao").toString():"0";
				vo.cal_jingyingjixiao = rs.getBigDecimal("cal_jingyingjixiao")!=null?rs.getBigDecimal("cal_jingyingjixiao").toString():"0";
				vo.pay_jingyingjixiao = rs.getBigDecimal("pay_jingyingjixiao")!=null?rs.getBigDecimal("pay_jingyingjixiao").toString():"0";
				vo.pay_value = rs.getBigDecimal("pay_value")!=null?rs.getBigDecimal("pay_value").toString():"0";
				vo.valid = rs.getInt("valid");
				vo.valid_gongzuojixiao = rs.getInt("valid_gongzuojixiao");
				vo.valid_jingyingjixiao = rs.getInt("valid_jingyingjixiao");				
				l.add(vo);
			}			
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}
	public List<EmployeeVo> getEmployeeByOperateYM(int orgId, int y, int m) {

		try{
			String sql = "select * from t_stat_employee_month " +
					"	where jingyingjixiaohesuandanyuan = "+orgId+
						" 	and year(stat_time) = "+y+
						"	and month(stat_time) = "+m+
						"	order by name";
			
			Connection conn = getConnect();
			
			ResultSet rs = queryData(conn,sql);
			List<EmployeeVo> l = new ArrayList<EmployeeVo>();
			while(rs.next()){
				EmployeeVo vo = new EmployeeVo();
				vo.id = rs.getInt("employee_id");
				vo.name = rs.getString("name");
				vo.jingyingjixiaohesuandanyuan= orgId;
				vo.work_id = rs.getString("work_id");
				vo.stat_time = DateTools.dtsJustDate(rs.getDate("stat_time"));
				vo.cal_gongzuojixiao = rs.getBigDecimal("cal_gongzuojixiao")!=null?rs.getBigDecimal("cal_gongzuojixiao").toString():"0";
				vo.pay_gongzuojixiao = rs.getBigDecimal("pay_gongzuojixiao")!=null?rs.getBigDecimal("pay_gongzuojixiao").toString():"0";
				vo.cal_jingyingjixiao = rs.getBigDecimal("cal_jingyingjixiao")!=null?rs.getBigDecimal("cal_jingyingjixiao").toString():"0";
				vo.pay_jingyingjixiao = rs.getBigDecimal("pay_jingyingjixiao")!=null?rs.getBigDecimal("pay_jingyingjixiao").toString():"0";
				vo.pay_value = rs.getBigDecimal("pay_value")!=null?rs.getBigDecimal("pay_value").toString():"0";
				vo.valid = rs.getInt("valid");
				vo.valid_gongzuojixiao = rs.getInt("valid_gongzuojixiao");
				vo.valid_jingyingjixiao = rs.getInt("valid_jingyingjixiao");				
				l.add(vo);
			}			
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public void setEmployeeInuse(EmployeeVo vo,int inuse) {
		try{
			String sql = "update t_sys_stat_employee set in_use = "+inuse+" where employee_id = "+vo.id;			
			Connection conn = getConnect();
			updateData(conn, sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void addEmployee(EmployeeVo vo) {
		try{
			Connection con = getConnect();
			ResultSet rs = queryData(con,"select case when max(employee_id)+1 is null then 1 else max(employee_id)+1 end as employee_id" +
					" from t_sys_stat_employee");	
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("employee_id");
			}
			
			String sql ="insert into t_sys_stat_employee" +
					"(employee_id,name,sfz,xztm,his_id,work_id,daoshixingming," +//7
					"xingbie,in_use,renyuanxingzhi,shifoufanpin," +//4
					"shenfen,yonggongzhidu,xingzhengkeshi,keshixingzhi," +//4
					"gongzuojixiaohesuandanyuan,jingyingjixiaohesuandanyuan,zhiweibiaozhunfen,fengxianxishu," +//4
					"xingzhengzhiwu1,xingzhengzhiwu2,xingzhengjibie,xingzhengshizhi," +//4
					"chushengriqi,ruzhishijian,ruyuanshijian," +//3
					"gangweimingcheng,gangweijibie,linchuangjishuma,teshubiaozhi,gangweileibie," +//5
					"jishuzhicheng,xianpinzhicheng,zhichengjibie,zhiyeleibie,zhuguankeshi,fenguankeshi)" +//6
					" values(?,?,?,?,?,?,?," +
					"?,?,?,?," +
					"?,?,?,?," +
					"?,?,?,?," +
					"?,?,?,?,?,?,?," +
					"?,?,?,?,?," +
					"?,?,?,?,?,?)";
			PreparedStatement pst =con.prepareStatement(sql);
			pst.setInt(1, maxID);pst.setString(2, vo.name);pst.setString(3, vo.sfz);pst.setString(4, vo.xztm);
			pst.setString(5, vo.his_id);pst.setString(6, vo.work_id);pst.setString(7, vo.daoshixingming);
			pst.setInt(8, vo.xingbie);pst.setInt(9, 1);pst.setInt(10, vo.renyuanxingzhi);pst.setInt(11, vo.shifoufanpin);
			pst.setInt(12, vo.shenfen);pst.setInt(13, vo.yonggongzhidu);pst.setInt(14, vo.xingzhengkeshi);pst.setInt(15, vo.keshixingzhi);
			pst.setInt(16, vo.gongzuojixiaohesuandanyuan);pst.setInt(17, vo.jingyingjixiaohesuandanyuan);
			pst.setBigDecimal(18, (vo.zhiweibiaozhunfen_str != null && !vo.zhiweibiaozhunfen_str.equals("")) ? new BigDecimal(vo.zhiweibiaozhunfen_str):null);
			pst.setBigDecimal(19, (vo.fengxianxishu_str != null&&!vo.fengxianxishu_str.equals(""))  ? new BigDecimal(vo.fengxianxishu_str):null);
			pst.setInt(20, vo.xingzhengzhiwu1);pst.setInt(21, vo.xingzhengzhiwu2);pst.setInt(22, vo.xingzhengjibie);pst.setInt(23, vo.xingzhengshizhi);
			pst.setDate(24, (vo.chushengriqi_str != null &&!vo.chushengriqi_str.equals("")) ? DateTools.UDtoSD(DateTools.stdJustDate(vo.chushengriqi_str)):null);
			pst.setDate(25, (vo.ruzhishijian_str != null&&!vo.ruzhishijian_str.equals("")) ? DateTools.UDtoSD(DateTools.stdJustDate(vo.ruzhishijian_str)):null);
			pst.setDate(26, (vo.ruyuanshijian_str != null&&!vo.ruyuanshijian_str.equals(""))? DateTools.UDtoSD(DateTools.stdJustDate(vo.ruyuanshijian_str)):null);
			pst.setInt(27, vo.gangweimingcheng);pst.setInt(28, vo.gangweijibie);pst.setString(29, vo.linchuangjishuma);
			pst.setInt(30, vo.teshubiaozhi);pst.setInt(31, vo.gangweileibie);
			pst.setInt(32, vo.jishuzhicheng);pst.setInt(33, vo.xianpinzhicheng);
			pst.setInt(34, vo.zhichengjibie);pst.setInt(35, vo.zhiyeleibie);
			pst.setString(36, vo.zhuguankeshi);pst.setString(37, vo.fenguankeshi);
			System.out.println(sql);
			pst.executeUpdate();
			freeConnection(con);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
	}

	public void setEmployeeGuanlikeshi(EmployeeVo vo,int zhuguan,int keshiid,int add) {
		try{
			Connection conn = getConnect();
			String sql = "";
			if(zhuguan == 1){//主管科室
				if(add == 1){//增加
					sql = "update t_sys_stat_employee set zhuguankeshi = zhuguankeshi+':"+keshiid+":' where employee_id = "+vo.id;
				}else{//取消
					sql = "update t_sys_stat_employee set zhuguankeshi = replace(zhuguankeshi,':"+keshiid+":','') where employee_id = "+vo.id;
				}				
			}else{//分管科室
				if(add == 1){//增加
					sql = "update t_sys_stat_employee set fenguankeshi = fenguankeshi+':"+keshiid+":' where employee_id = "+vo.id;
				}else{//取消
					sql = "update t_sys_stat_employee set fenguankeshi = replace(fenguankeshi,':"+keshiid+":','') where employee_id = "+vo.id;
				}
			}
			updateData(conn, sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
	}

	public void appEmployeeToMonth(String stat_time) {
		try{
			String sql = " select a.employee_id,a.name,a.sfz,a.xztm,a.his_id,a.work_id,a.daoshixingming,"  +
					" a.xingbie,a.in_use,a.renyuanxingzhi,a.shifoufanpin,"  +
					" a.shenfen,DICT_SHENFEN.name as shenfen_str,"  +
					" a.yonggongzhidu,DICT_YONGGONGZHIDU.name as yonggongzhidu_str,"  +
					" a.xingzhengkeshi,t_sys_stat_admin_department.name as xingzhengkeshi_str,"  +
					" a.keshixingzhi,DICT_KESHIXINGZHI.name as keshixingzhi_str,"  +
					" a.gongzuojixiaohesuandanyuan," +
					"	t_sys_stat_organization_vs_dispense_organization.name as gongzuojixiaohesuandanyuan_str,"  +
					" a.jingyingjixiaohesuandanyuan," +
					"	t_sys_stat_operate_department.name as jingyingjixiaohesuandanyuan_str,"  +
					" a.zhiweibiaozhunfen,a.fengxianxishu,"  +
					" a.xingzhengzhiwu1,b.name as xingzhengzhiwu1_str,"  +
					" a.xingzhengzhiwu2,c.name as xingzhengzhiwu2_str,"  +
					" a.xingzhengjibie,DICT_XINGZHENGJIBIE.name as xingzhengjibie_str,"  +
					" a.xingzhengshizhi,DICT_XINGZHENGSHIZHI.name as xingzhengshizhi_str,"  +
					" a.chushengriqi,a.ruzhishijian,a.ruyuanshijian,"  +
					" a.gangweimingcheng,DICT_GANGWEIMINGCHENG.name as gangweimingcheng_str,"  +
					" a.gangweijibie,a.linchuangjishuma,a.teshubiaozhi,"  +
					" a.gangweileibie,DICT_GANGWEILEIBIE.name as gangweileibie_str,"  +
					" a.jishuzhicheng,DICT_JISHUZHICHENG.name as jishuzhicheng_str,"  +
					" a.xianpinzhicheng,d.name as xianpinzhicheng_str,"  +
					" a.zhichengjibie,e.name as zhichengjibie_str,"  +
					" a.zhiyeleibie,DICT_DUTY_TYPE.name as zhiyeleibie_str,"  +
					" a.zhuguankeshi,a.fenguankeshi," +
					" a.jingyingjixiaogangweixishu,a.gongzuojixiaogangweixishu " +
					"	from t_sys_stat_employee as a"  +
					" left join DICT_SHENFEN"  +
					" on a.shenfen=DICT_SHENFEN.id"  +
					" left join DICT_YONGGONGZHIDU"  +
					" on a.yonggongzhidu = DICT_YONGGONGZHIDU.id"  +
					" left join t_sys_stat_admin_department"  +
					" on a.xingzhengkeshi = t_sys_stat_admin_department.id"  +
					" left join DICT_KESHIXINGZHI"  +
					" on a.keshixingzhi = DICT_KESHIXINGZHI.id"  +
					" left join t_sys_stat_organization_vs_dispense_organization"  +
					" on a.gongzuojixiaohesuandanyuan = t_sys_stat_organization_vs_dispense_organization.id"  +
					" left join t_sys_stat_operate_department"  +
					" on a.jingyingjixiaohesuandanyuan = t_sys_stat_operate_department.id"  +
					" left join DICT_XINGZHENGZHIWU as b"  +
					" on a.xingzhengzhiwu1 = b.id"  +
					" left join DICT_XINGZHENGZHIWU as c"  +
					" on a.xingzhengzhiwu2 = c.id"  +
					" left join DICT_XINGZHENGJIBIE"  +
					" on a.xingzhengjibie = DICT_XINGZHENGJIBIE.id"  +
					" left join DICT_XINGZHENGSHIZHI"  +
					" on a.xingzhengshizhi = DICT_XINGZHENGSHIZHI.id"  +
					" left join DICT_GANGWEIMINGCHENG"  +
					" on a.gangweimingcheng = DICT_GANGWEIMINGCHENG.id"  +
					" left join DICT_GANGWEILEIBIE"  +
					" on a.gangweileibie = DICT_GANGWEILEIBIE.id"  +
					" left join DICT_JISHUZHICHENG"  +
					" on a.jishuzhicheng = DICT_JISHUZHICHENG.id"  +
					" left join DICT_ZHICHENGJIBIE as d"  +
					" on a.xianpinzhicheng = d.id"  +
					" left join DICT_ZHICHENGJIBIE as e"  +
					" on a.zhichengjibie = e.id"  +
					" left join DICT_DUTY_TYPE"  +
					" on a.zhiyeleibie = DICT_DUTY_TYPE.id" +
					" where a.in_use = 1";
			
			Connection conn = getConnect();
//			String sqlDel = "delete t_stat_employee_month where stat_time = '"+stat_time+"' and valid = 0";
//			updateData(conn, sqlDel);
//			conn.setAutoCommit(false);
			ResultSet rs = queryData(conn,sql);
			

			
			while(rs.next()){
				EmployeeVo vo = new EmployeeVo();
				vo.stat_time = stat_time;
				vo.id = rs.getInt("employee_id");
				vo.name = rs.getString("name");
				vo.xingbie = rs.getInt("xingbie");
				vo.sfz = rs.getString("sfz");
				vo.xztm = rs.getString("xztm");
				vo.his_id = rs.getString("his_id");
				vo.work_id = rs.getString("work_id");
				vo.in_use = rs.getInt("in_use");
				vo.shenfen = rs.getInt("shenfen");
				vo.shenfen_str = rs.getString("shenfen_str");
				vo.renyuanxingzhi = rs.getInt("renyuanxingzhi");
				vo.shifoufanpin = rs.getInt("shifoufanpin");
				vo.yonggongzhidu = rs.getInt("yonggongzhidu");
				vo.yonggongzhidu_str = rs.getString("yonggongzhidu_str");
				vo.daoshixingming = rs.getString("daoshixingming");
				vo.xingzhengkeshi = rs.getInt("xingzhengkeshi");
				vo.xingzhengkeshi_str = rs.getString("xingzhengkeshi_str");
				vo.keshixingzhi = rs.getInt("keshixingzhi");
				vo.keshixingzhi_str = rs.getString("keshixingzhi_str");
				vo.gongzuojixiaohesuandanyuan = rs.getInt("gongzuojixiaohesuandanyuan");
				vo.gongzuojixiaohesuandanyuan_str = rs.getString("gongzuojixiaohesuandanyuan_str");//getOrganizationName(orgList,rs.getInt("gongzuojixiaohesuandanyuan"));
				vo.jingyingjixiaohesuandanyuan = rs.getInt("jingyingjixiaohesuandanyuan");
				vo.jingyingjixiaohesuandanyuan_str = rs.getString("jingyingjixiaohesuandanyuan_str");
				if(rs.getString("zhiweibiaozhunfen") != null){
					vo.zhiweibiaozhunfen_str = rs.getBigDecimal("zhiweibiaozhunfen").toString();
				}
				if(rs.getString("fengxianxishu") != null){
					vo.fengxianxishu_str = rs.getBigDecimal("fengxianxishu").toString();
				}
				
				vo.xingzhengzhiwu1 = rs.getInt("xingzhengzhiwu1");
				vo.xingzhengzhiwu1_str = rs.getString("xingzhengzhiwu1_str");
				vo.xingzhengzhiwu2 = rs.getInt("xingzhengzhiwu2");
				vo.xingzhengzhiwu2_str = rs.getString("xingzhengzhiwu2_str");
				vo.xingzhengjibie = rs.getInt("xingzhengjibie");
				vo.xingzhengjibie_str = rs.getString("xingzhengjibie_str");
				vo.xingzhengshizhi = rs.getInt("xingzhengshizhi");
				vo.xingzhengshizhi_str = rs.getString("xingzhengshizhi_str");
				vo.chushengriqi_str = DateTools.dtsJustDate(rs.getDate("chushengriqi"));
				vo.ruzhishijian_str = DateTools.dtsJustDate(rs.getDate("ruzhishijian"));
				vo.ruyuanshijian_str = DateTools.dtsJustDate(rs.getDate("ruyuanshijian"));
				vo.gangweimingcheng = rs.getInt("gangweimingcheng");
				vo.gangweimingcheng_str = rs.getString("gangweimingcheng_str");
				vo.gangweijibie = rs.getInt("gangweijibie");
				vo.gangweileibie = rs.getInt("gangweileibie");
				vo.gangweileibie_str = rs.getString("gangweileibie_str");
				vo.linchuangjishuma = rs.getString("linchuangjishuma");
				vo.jishuzhicheng = rs.getInt("jishuzhicheng");
				vo.jishuzhicheng_str = rs.getString("jishuzhicheng_str");
				vo.xianpinzhicheng = rs.getInt("xianpinzhicheng");
				vo.xianpinzhicheng_str = rs.getString("xianpinzhicheng_str");
				vo.zhichengjibie = rs.getInt("zhichengjibie");
				vo.zhichengjibie_str = rs.getString("zhichengjibie_str");
				vo.zhiyeleibie = rs.getInt("zhiyeleibie");
				vo.zhiyeleibie_str = rs.getString("zhiyeleibie_str");
				vo.teshubiaozhi = rs.getInt("teshubiaozhi");
				vo.zhuguankeshi = rs.getString("zhuguankeshi");
				vo.fenguankeshi = rs.getString("fenguankeshi");
				vo.zerenjintie = getZeRenJinTie(conn, vo).toString();
				vo.zhiweijintie = getZhiWeiJinTie(conn, vo).toString();
				vo.yuanlingjintie = getYuanLingJinTie(conn, vo).toString();
				vo.gongzuojixiaogangweixishu = rs.getBigDecimal("gongzuojixiaogangweixishu");
				vo.jingyingjixiaogangweixishu = rs.getBigDecimal("jingyingjixiaogangweixishu");
				
				String checkExistSQL = "select * from t_stat_employee_month where employee_id = "+vo.id+
						"	and stat_time = '"+stat_time+"'";
				ResultSet rs2 = queryData(conn, checkExistSQL);
				if(rs2.next()){//存在
					if(rs2.getInt("valid") != 1 && rs2.getInt("valid_gongzuojixiao") != 1 && rs2.getInt("valid_jingyingjixiao") != 1){
						System.out.println("都未验证，可以更新!");
						String updateSQL = "update t_stat_employee_month set " +
						"cal_gongzuojixiao= "+(vo.cal_gongzuojixiao !=null && !"".equals(vo.cal_gongzuojixiao)?vo.cal_gongzuojixiao:"0")+","+
						"cal_jingyingjixiao= "+(vo.cal_jingyingjixiao !=null && !"".equals(vo.cal_jingyingjixiao)?vo.cal_jingyingjixiao:"0")+","+
						"chushengriqi= "+(vo.chushengriqi_str !=null && !"".equals(vo.chushengriqi_str)?"'"+vo.chushengriqi_str+"'":"NULL")+","+
						"daoshixingming= "+(vo.daoshixingming !=null && !"".equals(vo.daoshixingming)?"'"+vo.daoshixingming+"'":"NULL")+","+
						"employee_id= "+(vo.id)+","+
						"fenguankeshi= "+(vo.fenguankeshi !=null && !"".equals(vo.fenguankeshi)?"'"+vo.fenguankeshi+"'":"NULL")+","+
						"fengxianxishu= "+(vo.fengxianxishu_str !=null && !"".equals(vo.fengxianxishu_str)?vo.fengxianxishu_str:"0")+","+
						"gangweijibie= "+(vo.gangweijibie)+","+
						"gangweileibie= "+(vo.gangweileibie)+","+
						"gangweimingcheng= "+(vo.gangweimingcheng)+","+
						"gongzuojixiaogangweixishu= "+(vo.gongzuojixiaogangweixishu !=null && !"".equals(vo.gongzuojixiaogangweixishu)?vo.gongzuojixiaogangweixishu:"0")+","+
						"gongzuojixiaohesuandanyuan= "+(vo.gongzuojixiaohesuandanyuan)+","+
						"his_id= "+(vo.his_id)+","+
						"in_use= "+(vo.in_use)+","+
						"jingyingjixiaogangweixishu= "+(vo.jingyingjixiaogangweixishu !=null && !"".equals(vo.jingyingjixiaogangweixishu)?vo.jingyingjixiaogangweixishu:"0")+","+
						"jingyingjixiaohesuandanyuan= "+(vo.jingyingjixiaohesuandanyuan)+","+
						"jishuzhicheng= "+(vo.jishuzhicheng)+","+
						"keshixingzhi= "+(vo.keshixingzhi)+","+
						"linchuangjishuma= "+(vo.linchuangjishuma !=null && !"".equals(vo.linchuangjishuma)?"'"+vo.linchuangjishuma+"'":"NULL")+","+
						"name= "+(vo.name !=null && !"".equals(vo.name)?"'"+vo.name+"'":"NULL")+","+
						"pay_gongzuojixiao= "+(vo.pay_gongzuojixiao !=null && !"".equals(vo.pay_gongzuojixiao)?vo.pay_gongzuojixiao:"0")+","+
						"pay_jingyingjixiao= "+(vo.pay_jingyingjixiao !=null && !"".equals(vo.pay_jingyingjixiao)?vo.pay_jingyingjixiao:"0")+","+
						"pay_value= "+(vo.pay_value !=null && !"".equals(vo.pay_value)?vo.pay_value:"0")+","+
						"renyuanxingzhi= "+(vo.renyuanxingzhi)+","+
						"ruyuanshijian= "+(vo.ruyuanshijian_str !=null && !"".equals(vo.ruyuanshijian_str)?"'"+vo.ruyuanshijian_str+"'":"NULL")+","+
						"ruzhishijian= "+(vo.ruzhishijian_str !=null && !"".equals(vo.ruzhishijian_str)?"'"+vo.ruzhishijian_str+"'":"NULL")+","+
						"sfz= "+(vo.sfz !=null && !"".equals(vo.sfz)?"'"+vo.sfz+"'":"NULL")+","+
						"shenfen= "+(vo.shenfen)+","+
						"shifoufanpin= "+(vo.shifoufanpin)+","+
						"stat_time= "+(vo.stat_time !=null && !"".equals(vo.stat_time)?"'"+vo.stat_time+"'":"NULL")+","+
						"teshubiaozhi= "+(vo.teshubiaozhi)+","+
						"valid= 0,"+
						"valid_gongzuojixiao= 0,"+
						"valid_jingyingjixiao= 0,"+
						"work_id= "+(vo.work_id !=null && !"".equals(vo.work_id)?"'"+vo.work_id+"'":"NULL")+","+
						"xianpinzhicheng= "+(vo.xianpinzhicheng)+","+
						"xingbie= "+(vo.xingbie)+","+
						"xingzhengjibie= "+(vo.xingzhengjibie)+","+
						"xingzhengkeshi= "+(vo.xingzhengkeshi)+","+
						"xingzhengshizhi= "+(vo.xingzhengshizhi)+","+
						"xingzhengzhiwu1= "+(vo.xingzhengzhiwu1)+","+
						"xingzhengzhiwu2= "+(vo.xingzhengzhiwu2)+","+
						"xztm= "+(vo.xztm !=null && !"".equals(vo.xztm)?"'"+vo.xztm+"'":"NULL")+","+
						"yonggongzhidu= "+(vo.yonggongzhidu)+","+
						"yuanlingjintie= "+(vo.yuanlingjintie !=null && !"".equals(vo.yuanlingjintie)?vo.yuanlingjintie:"NULL")+","+
						"zerenjintie= "+(vo.zerenjintie !=null && !"".equals(vo.zerenjintie)?vo.zerenjintie:"NULL")+","+
						"zhichengjibie= "+(vo.zhichengjibie)+","+
						"zhiweibiaozhunfen= "+(vo.zhiweibiaozhunfen_str !=null && !"".equals(vo.zhiweibiaozhunfen_str)?vo.zhiweibiaozhunfen_str:"NULL")+","+
						"zhiweijintie= "+(vo.zhiweijintie !=null && !"".equals(vo.zhiweijintie)?vo.zhiweijintie:"NULL")+","+
						"zhiyeleibie= "+(vo.zhiyeleibie)+","+
						"zhuguankeshi= "+(vo.zhuguankeshi !=null && !"".equals(vo.zhuguankeshi)?"'"+vo.zhuguankeshi+"'":"NULL")+
						"	where employee_id = "+vo.id+
						"	and stat_time = '"+stat_time+"'";		
						updateData(conn, updateSQL);
					}
					continue;
//					pst.setInt(1, vo.id);pst.setString(2, vo.name);pst.setString(3, vo.sfz);pst.setString(4, vo.xztm);
//					pst.setString(5, vo.his_id);pst.setString(6, vo.work_id);pst.setString(7, vo.daoshixingming);
//					pst.setInt(8, vo.xingbie);pst.setInt(9, 1);pst.setInt(10, vo.renyuanxingzhi);pst.setInt(11, vo.shifoufanpin);
//					pst.setInt(12, vo.shenfen);pst.setInt(13, vo.yonggongzhidu);pst.setInt(14, vo.xingzhengkeshi);pst.setInt(15, vo.keshixingzhi);
//					pst.setInt(16, vo.gongzuojixiaohesuandanyuan);pst.setInt(17, vo.jingyingjixiaohesuandanyuan);
//					pst.setBigDecimal(18, (vo.zhiweibiaozhunfen_str != null && !vo.zhiweibiaozhunfen_str.equals("")) ? new BigDecimal(vo.zhiweibiaozhunfen_str):null);
//					pst.setBigDecimal(19, (vo.fengxianxishu_str != null&&!vo.fengxianxishu_str.equals(""))  ? new BigDecimal(vo.fengxianxishu_str):null);
//					pst.setInt(20, vo.xingzhengzhiwu1);pst.setInt(21, vo.xingzhengzhiwu2);pst.setInt(22, vo.xingzhengjibie);pst.setInt(23, vo.xingzhengshizhi);
//					pst.setDate(24, (vo.chushengriqi_str != null &&!vo.chushengriqi_str.equals("")) ? DateTools.UDtoSD(DateTools.stdJustDate(vo.chushengriqi_str)):null);
//					pst.setDate(25, (vo.ruzhishijian_str != null&&!vo.ruzhishijian_str.equals("")) ? DateTools.UDtoSD(DateTools.stdJustDate(vo.ruzhishijian_str)):null);
//					pst.setDate(26, (vo.ruyuanshijian_str != null&&!vo.ruyuanshijian_str.equals(""))? DateTools.UDtoSD(DateTools.stdJustDate(vo.ruyuanshijian_str)):null);
//					pst.setInt(27, vo.gangweimingcheng);pst.setInt(28, vo.gangweijibie);pst.setString(29, vo.linchuangjishuma);
//					pst.setInt(30, vo.teshubiaozhi);pst.setInt(31, vo.gangweileibie);
//					pst.setInt(32, vo.jishuzhicheng);pst.setInt(33, vo.xianpinzhicheng);
//					pst.setInt(34, vo.zhichengjibie);pst.setInt(35, vo.zhiyeleibie);
//					pst.setString(36, vo.zhuguankeshi);pst.setString(37, vo.fenguankeshi);
//					pst.setBigDecimal(38, new BigDecimal(0));pst.setBigDecimal(39, new BigDecimal(0));
//					pst.setBigDecimal(40, new BigDecimal(0));pst.setBigDecimal(41, new BigDecimal(0));
//					pst.setInt(42, 0);
//					pst.setBigDecimal(43, new BigDecimal(vo.zerenjintie));
//					pst.setBigDecimal(44, new BigDecimal(vo.zhiweijintie));
//					pst.setBigDecimal(45, new BigDecimal(vo.yuanlingjintie));
//					pst.setBigDecimal(46, vo.gongzuojixiaogangweixishu);
//					pst.setBigDecimal(47, vo.jingyingjixiaogangweixishu);
//					System.out.println(insertSQL);
//					pst.addBatch();
//					System.out.println(rs2.getInt("employee_id")+" 已经存在并且应经审核！");
				}else{
					System.out.println("不存在");
					String insertSQL ="insert into t_stat_employee_month" +
					"(employee_id,name,sfz,xztm,his_id,work_id,daoshixingming," +//7
					"xingbie,in_use,renyuanxingzhi,shifoufanpin," +//4
					"shenfen,yonggongzhidu,xingzhengkeshi,keshixingzhi," +//4
					"gongzuojixiaohesuandanyuan,jingyingjixiaohesuandanyuan,zhiweibiaozhunfen,fengxianxishu," +//4
					"xingzhengzhiwu1,xingzhengzhiwu2,xingzhengjibie,xingzhengshizhi," +//4
					"chushengriqi,ruzhishijian,ruyuanshijian," +//3
					"gangweimingcheng,gangweijibie,linchuangjishuma,teshubiaozhi,gangweileibie," +//5
					"jishuzhicheng,xianpinzhicheng,zhichengjibie,zhiyeleibie,zhuguankeshi,fenguankeshi," +//6
					"cal_jingyingjixiao,pay_jingyingjixiao,cal_gongzuojixiao,pay_gongzuojixiao,valid,stat_time," +
					"zerenjintie,zhiweijintie,yuanlingjintie,gongzuojixiaogangweixishu,jingyingjixiaogangweixishu," +//6
					"valid_gongzuojixiao,valid_jingyingjixiao)"+
							"	values("+vo.id+
							","+(vo.name!=null&&!"".equals(vo.name)?("'"+vo.name+"'"):"NULL")+
							","+(vo.sfz!=null&&!"".equals(vo.sfz)?("'"+vo.sfz+"'"):"NULL")+
							","+(vo.xztm!=null&&!"".equals(vo.xztm)?("'"+vo.xztm+"'"):"NULL")+
							","+(vo.his_id!=null&&!"".equals(vo.his_id)?("'"+vo.his_id+"'"):"NULL")+
							","+(vo.work_id!=null&&!"".equals(vo.work_id)?("'"+vo.work_id+"'"):"NULL")+
							","+(vo.daoshixingming!=null&&!"".equals(vo.daoshixingming)?("'"+vo.daoshixingming+"'"):"NULL")+//7
							","+(vo.xingbie)+","+(vo.in_use)+","+(vo.renyuanxingzhi)+","+(vo.shifoufanpin)+//4
							","+(vo.shenfen)+","+(vo.yonggongzhidu)+","+(vo.xingzhengkeshi)+","+(vo.keshixingzhi)+//4
							","+(vo.gongzuojixiaohesuandanyuan)+","+(vo.jingyingjixiaohesuandanyuan)+
							","+(vo.zhiweibiaozhunfen_str!=null&&!"".equals(vo.zhiweibiaozhunfen_str)?vo.zhiweibiaozhunfen_str:"0")+
							","+(vo.fengxianxishu_str!=null&&!"".equals(vo.fengxianxishu_str)?vo.fengxianxishu_str:"0")+//4
							","+(vo.xingzhengzhiwu1)+","+(vo.xingzhengzhiwu2)+","+(vo.xingzhengjibie)+","+(vo.xingzhengshizhi)+//4
							","+(vo.chushengriqi_str!=null&&!"".equals(vo.chushengriqi_str)?("'"+DateTools.UDtoSD(DateTools.stdJustDate(vo.chushengriqi_str))+"'"):"NULL")+
							","+(vo.ruzhishijian_str!=null&&!"".equals(vo.ruzhishijian_str)?("'"+DateTools.UDtoSD(DateTools.stdJustDate(vo.ruzhishijian_str))+"'"):"NULL")+
							","+(vo.ruyuanshijian_str!=null&&!"".equals(vo.ruyuanshijian_str)?("'"+DateTools.UDtoSD(DateTools.stdJustDate(vo.ruyuanshijian_str))+"'"):"NULL")+//3
							","+(vo.gangweimingcheng)+","+(vo.gangweijibie)+
							","+(vo.linchuangjishuma!=null&&!"".equals(vo.linchuangjishuma)?("'"+vo.linchuangjishuma+"'"):"NULL")+
							","+(vo.teshubiaozhi)+","+(vo.gangweileibie)+//5
							","+(vo.jishuzhicheng)+","+(vo.xianpinzhicheng)+","+(vo.zhichengjibie)+","+(vo.zhiyeleibie)+
							","+(vo.zhuguankeshi!=null&&!"".equals(vo.zhuguankeshi)?("'"+vo.zhuguankeshi+"'"):"NULL")+
							","+(vo.fenguankeshi!=null&&!"".equals(vo.fenguankeshi)?("'"+vo.fenguankeshi+"'"):"NULL")+//6
							","+(vo.cal_jingyingjixiao!=null&&!"".equals(vo.cal_jingyingjixiao)?vo.cal_jingyingjixiao:"NULL")+
							","+(vo.pay_jingyingjixiao!=null&&!"".equals(vo.pay_jingyingjixiao)?vo.pay_jingyingjixiao:"NULL")+
							","+(vo.cal_gongzuojixiao!=null&&!"".equals(vo.cal_gongzuojixiao)?vo.cal_gongzuojixiao:"NULL")+
							","+(vo.pay_gongzuojixiao!=null&&!"".equals(vo.pay_gongzuojixiao)?vo.pay_gongzuojixiao:"NULL")+
							",0"+
							","+(vo.stat_time!=null&&!"".equals(vo.name)?("'"+vo.stat_time+"'"):"NULL")+//6
							","+(vo.zerenjintie!=null&&!"".equals(vo.zerenjintie)?vo.zerenjintie:"NULL")+
							","+(vo.zhiweijintie!=null&&!"".equals(vo.zhiweijintie)?vo.zhiweijintie:"NULL")+
							","+(vo.yuanlingjintie!=null&&!"".equals(vo.yuanlingjintie)?vo.yuanlingjintie:"NULL")+
							","+(vo.gongzuojixiaogangweixishu !=null && !"".equals(vo.gongzuojixiaogangweixishu)?vo.gongzuojixiaogangweixishu:"0")+
							","+(vo.jingyingjixiaogangweixishu !=null && !"".equals(vo.jingyingjixiaogangweixishu)?vo.jingyingjixiaogangweixishu:"0")+
							",0,0)";
					
					updateData(conn, insertSQL);
					
				}
			}	
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	private BigDecimal getZeRenJinTie(Connection conn,EmployeeVo vo){
		try{
			if(vo.xingzhengzhiwu1 == 0 && vo.xingzhengzhiwu2 == 0){
				return new BigDecimal(0);
			}
			int jibie1 = 0;
			BigDecimal fee1 = new BigDecimal(0);
			if(vo.xingzhengzhiwu1 != 0){
				String sql = "select * from DICT_XINGZHENGZHIWU where id = "+vo.xingzhengzhiwu1;
				ResultSet rs = queryData(conn, sql);
				if(rs.next()){
					jibie1 = rs.getInt("jibie");
					fee1 = rs.getBigDecimal("fee");
				}
			}
			int jibie2 = 0;
			BigDecimal fee2 = new BigDecimal(0);
			if(vo.xingzhengzhiwu2 != 0){
				String sql = "select * from DICT_XINGZHENGZHIWU where id = "+vo.xingzhengzhiwu2;
				ResultSet rs = queryData(conn, sql);
				if(rs.next()){
					jibie2 = rs.getInt("jibie");
					fee2 = rs.getBigDecimal("fee");
				}
			}
			int jibie = 0;
			BigDecimal fee = new BigDecimal(0);
			if(jibie1 > jibie2){//用级别1
				jibie = jibie1;
				fee = fee1;
			}else{//用级别2
				jibie = jibie2;
				fee = fee2;
			}
			
			if(jibie == 1 || jibie == 2){//最高级别  用分管科室
				String ks[] = vo.fenguankeshi.replaceAll("::", ":").split(":");
				BigDecimal total = new BigDecimal(0); 
				int count = 0;
				for(int i =0;i<ks.length;i++){
					String s = ks[i];
					if(s.equals("")){
						continue;
					}else{
						count++;
						String sql = "select dbo.FUN_VOTE_ADMIN("+s+",'"+vo.stat_time+"','0','1',1)";
						ResultSet rs = queryData(conn, sql);
						if(rs.next()){
							total = total.add(rs.getBigDecimal(1));
						}
					}
				}
				System.out.println("数量： "+count);
				System.out.println("总额： "+total);
				if(total.compareTo(new BigDecimal(0)) == 0){
					fee = new BigDecimal(0);
				}else{
					fee = fee.multiply(total.divide(new BigDecimal(count),2,BigDecimal.ROUND_HALF_UP));
				}				
			}else if(jibie == 3){//第二级别  用主管科室
				String ks[] = vo.zhuguankeshi.replaceAll("::", ":").split(":");
				BigDecimal total = new BigDecimal(0); 
				int count = 0;
				for(int i =0;i<ks.length;i++){
					String s = ks[i];
					if(s.equals("")){
						continue;
					}else{
						count++;
						String sql = "select dbo.FUN_VOTE_ADMIN("+s+",'"+vo.stat_time+"','0','1',1)";
						ResultSet rs = queryData(conn, sql);
						if(rs.next()){
							total = total.add(rs.getBigDecimal(1));
						}
					}
				}
				System.out.println("数量： "+count);
				System.out.println("总额： "+total);
				if(total.compareTo(new BigDecimal(0)) == 0){
					fee = new BigDecimal(0);
				}else{
					fee = fee.multiply(total.divide(new BigDecimal(count),2,BigDecimal.ROUND_HALF_UP));
				}				
			}else{//最低级别，用行政科室
				String sql = "select dbo.FUN_VOTE_ADMIN("+vo.xingzhengkeshi+",'"+vo.stat_time+"','0','1',1)";
				ResultSet rs = queryData(conn, sql);
				if(rs.next()){
					fee = fee.multiply(rs.getBigDecimal(1));
				}				
			}
			return fee;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return new BigDecimal(0);
	}

	private BigDecimal getZhiWeiJinTie(Connection conn,EmployeeVo vo){
		try{
			if(vo.renyuanxingzhi == 0){
				return new BigDecimal(0);
			}
			String sql1 = "select fee from DICT_XINGZHENGJIBIE where id = "+vo.xingzhengjibie;
			String sql2 = "select fee from DICT_ZHICHENGJIBIE where id = "+vo.xianpinzhicheng;
			ResultSet rs1 = queryData(conn, sql1);
			BigDecimal xingzhengjibie = new BigDecimal(0);
			if(rs1.next()){
				xingzhengjibie = rs1.getBigDecimal(1);
			}
			
			ResultSet rs2 = queryData(conn, sql2);
			BigDecimal xianpinzhicheng = new BigDecimal(0);			
			if(rs2.next()){
				xianpinzhicheng = rs2.getBigDecimal(1);
			}
			
			if(xingzhengjibie.compareTo(xianpinzhicheng) > 0){
				return xingzhengjibie;
			}else{
				return xianpinzhicheng;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return new BigDecimal(0);
	}
	private BigDecimal getYuanLingJinTie(Connection conn,EmployeeVo vo){
		try{
			if(vo.renyuanxingzhi == 0 && vo.shifoufanpin == 0){
				return new BigDecimal(0);
			}
			int y = Calendar.getInstance().getTime().getYear()+1900-Integer.valueOf(vo.ruyuanshijian_str.split("-")[0])+1;
			return new BigDecimal(30*y);
		}catch(Exception ex){
			System.out.println(vo.id+"   院龄津贴＝0");
//			ex.printStackTrace();
		}
		return new BigDecimal(0);
	}
	public void updateEmployee(EmployeeVo vo) {
		try{
			Connection con = getConnect();
			
			String sql ="update t_sys_stat_employee" +
					"	set name=?,sfz=?,xztm=?,his_id=?,work_id=?,daoshixingming=?," +//7
					"xingbie=?,renyuanxingzhi=?,shifoufanpin=?," +//4
					"shenfen=?,yonggongzhidu=?,xingzhengkeshi=?,keshixingzhi=?," +//4
					"gongzuojixiaohesuandanyuan=?,jingyingjixiaohesuandanyuan=?," +
					"zhiweibiaozhunfen=?,fengxianxishu=?," +//4
					"xingzhengzhiwu1=?,xingzhengzhiwu2=?,xingzhengjibie=?,xingzhengshizhi=?," +//4
					"chushengriqi=?,ruzhishijian=?,ruyuanshijian=?," +//3
					"gangweimingcheng=?,gangweijibie=?,linchuangjishuma=?,teshubiaozhi=?,gangweileibie=?," +//5
					"jishuzhicheng=?,xianpinzhicheng=?,zhichengjibie=?,zhiyeleibie=?,zhuguankeshi=?,fenguankeshi=?" +
					"	where employee_id = " +vo.id;
			PreparedStatement pst =con.prepareStatement(sql);
			pst.setString(1, vo.name);pst.setString(2, vo.sfz);pst.setString(3, vo.xztm);
			pst.setString(4, vo.his_id);pst.setString(5, vo.work_id);pst.setString(6, vo.daoshixingming);
			pst.setInt(7, vo.xingbie);pst.setInt(8, vo.renyuanxingzhi);pst.setInt(9, vo.shifoufanpin);
			pst.setInt(10, vo.shenfen);pst.setInt(11, vo.yonggongzhidu);
			pst.setInt(12, vo.xingzhengkeshi);pst.setInt(13, vo.keshixingzhi);
			pst.setInt(14, vo.gongzuojixiaohesuandanyuan);pst.setInt(15, vo.jingyingjixiaohesuandanyuan);
			pst.setBigDecimal(16, (vo.zhiweibiaozhunfen_str != null && !vo.zhiweibiaozhunfen_str.equals("")) ? new BigDecimal(vo.zhiweibiaozhunfen_str):null);
			pst.setBigDecimal(17, (vo.fengxianxishu_str != null&&!vo.fengxianxishu_str.equals(""))  ? new BigDecimal(vo.fengxianxishu_str):null);
			pst.setInt(18, vo.xingzhengzhiwu1);pst.setInt(19, vo.xingzhengzhiwu2);
			pst.setInt(20, vo.xingzhengjibie);pst.setInt(21, vo.xingzhengshizhi);
			pst.setDate(22, (vo.chushengriqi_str != null &&!vo.chushengriqi_str.equals("")) ? DateTools.UDtoSD(DateTools.stdJustDate(vo.chushengriqi_str)):null);
			pst.setDate(23, (vo.ruzhishijian_str != null&&!vo.ruzhishijian_str.equals("")) ? DateTools.UDtoSD(DateTools.stdJustDate(vo.ruzhishijian_str)):null);
			pst.setDate(24, (vo.ruyuanshijian_str != null&&!vo.ruyuanshijian_str.equals(""))? DateTools.UDtoSD(DateTools.stdJustDate(vo.ruyuanshijian_str)):null);
			pst.setInt(25, vo.gangweimingcheng);pst.setInt(26, vo.gangweijibie);pst.setString(27, vo.linchuangjishuma);
			pst.setInt(28, vo.teshubiaozhi);pst.setInt(29, vo.gangweileibie);
			pst.setInt(30, vo.jishuzhicheng);pst.setInt(31, vo.xianpinzhicheng);
			pst.setInt(32, vo.zhichengjibie);pst.setInt(33, vo.zhiyeleibie);
			pst.setString(34, vo.zhuguankeshi);pst.setString(35, vo.fenguankeshi);
			System.out.println(sql);
			pst.executeUpdate();
			con.commit();
			freeConnection(con);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public String validGongZuoJiXiao(List<EmployeeVo> lst) {
		try{
			Connection conn = getConnect();
			for(int i =0;i<lst.size();i++){
				EmployeeVo vo = lst.get(i);
				String updateSQL = "UPDATE t_stat_employee_month" +
						"	set valid_gongzuojixiao = 1" +
						"	where employee_id = "+vo.id+
						"	and gongzuojixiaohesuandanyuan = "+vo.gongzuojixiaohesuandanyuan+
						"	and stat_time = '"+vo.stat_time+"'";
				updateData(conn, updateSQL);
			}
			return _SUCCEEDED;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return _FAILED;
	}

	public String validJingYingJiXiao(List<EmployeeVo> lst) {
		try{
			Connection conn = getConnect();
			for(int i =0;i<lst.size();i++){
				EmployeeVo vo = lst.get(i);
				String updateSQL = "UPDATE t_stat_employee_month" +
						"	set valid_jingyingjixiao = 1" +
						"	where employee_id = "+vo.id+
						"	and jingyingjixiaohesuandanyuan = "+vo.jingyingjixiaohesuandanyuan+
						"	and stat_time = '"+vo.stat_time+"'";
				updateData(conn, updateSQL);
			}
			return _SUCCEEDED;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return _FAILED;
	}


}
